﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Import_PartReceive : Form
    {        
        private DataTable ImportTable;

        public frm_Import_PartReceive()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            
            this.ImportTable = new DataTable();
            this.ImportTable.Columns.Add("POID", typeof(string));
            this.ImportTable.Columns.Add("OrderNo", typeof(string));
            this.ImportTable.Columns.Add("Quantity", typeof(string));
            this.ImportTable.Columns.Add("ArrivalDate", typeof(string));
            this.ImportTable.Columns.Add("Note", typeof(string));
            this.ImportTable.Columns.Add("Full", typeof(int));

        }

        private void ImportExcel()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();

            bool have_data = false;

            importFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                // Import new data from Excel
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [POID], [OrderNo], [Quantity], [ArrivalDate],"
                        + "[Note] from [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Get data error: " + ex.Message);
                    //MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }

            if (have_data)
            {
                string err_text = null;
                foreach (DataRow excel_row in excel_data.Rows)
                {
                    bool pass = true;
                    string poid = excel_row["POID"].ToString().Trim();
                    string orderno = excel_row["OrderNo"].ToString().Trim();
                    string arrivaldate = excel_row["ArrivalDate"].ToString();
                    DateTime arr = new DateTime();

                    if (!checkOrderExist(orderno, poid))
                    {
                        err_text += "OrderNo " + orderno + ": Not Exist \n\r";
                        pass = false;
                    }

                    if (pass)
                    {
                        if (string.IsNullOrEmpty(arrivaldate))
                        {
                            err_text += "OrderNo " + orderno + ": No ArrivalDate \n\r";
                            pass = false;
                        }
                        else
                        {
                            if (!DateTime.TryParse(arrivaldate, out arr))
                            {
                                err_text += "OrderNo " + orderno + ": Invalid ArrivalDate \n\r";
                                pass = false;
                            }
                        }
                    }

                    if (pass)
                    {
                        string note = excel_row["Note"].ToString();
                        float quantity = 0;
                        if (float.TryParse(excel_row["Quantity"].ToString().Trim(), out quantity))
                        {
                            if (quantity > 0)
                            {
                                float totalreceive = quantity + getTotalQuantityArrived(orderno, poid);
                                float totalquantity = getTotalQuantityOrdered(orderno, poid);
                                if (totalreceive > totalquantity)
                                {
                                    err_text += "OrderNo " + orderno + ": Quantity remain only " + (totalquantity + quantity - totalreceive).ToString() + " \n\r";
                                    pass = false;
                                }
                                else
                                {
                                    DataRow new_row = this.ImportTable.NewRow();
                                    new_row["POID"] = poid;
                                    new_row["OrderNo"] = orderno;
                                    new_row["Quantity"] = quantity;
                                    new_row["ArrivalDate"] = arrivaldate;
                                    new_row["Note"] = note;
                                    if (totalreceive < totalquantity)
                                    {
                                        new_row["Full"] = 0;
                                    }
                                    else
                                    {
                                        new_row["Full"] = 1;
                                    }
                                    ImportTable.Rows.Add(new_row);
                                }
                            }
                            else
                            {
                                err_text += "OrderNo " + orderno + ": Quantity must be greater than 0 \n\r";
                                pass = false;
                            }
                        }
                        else
                        {
                            err_text += "OrderNo " + orderno + ": Invalid quantity \n\r";
                            pass = false;
                        }
                    }
                }

                gridPartReceive.DataSource = ImportTable;
                PartReceive.BestFitColumns();

                if (!string.IsNullOrEmpty(err_text))
                {
                    MessageBox.Show(err_text);
                }
            }
        }
        
        ////////////////////////////////////////////////
        /// Load Order No list to form
        ////////////////////////////////////////////////
        private bool checkOrderExist(string order, string po)
        {
            bool ret = false;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT COUNT(OrderNo) FROM [241_PODetails]"
                    + " WHERE OrderNo = '" + order + "' AND POID = '" + po + "' AND Finished=0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    if (!string.IsNullOrEmpty(dataReader[0].ToString()))
                    {
                        if (Int32.Parse(dataReader[0].ToString()) > 0)
                        {
                            ret = true;
                        }
                    }
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = false;
                MessageBox.Show("Cannot get OrderNo list");                
            }

            return ret;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }        
        
        ///////////////////////////
        /// When Click Add Button
        ///////////////////////////        
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            string err = null;

            if (ImportTable.Rows.Count > 0)
            {
                foreach (DataRow add_row in ImportTable.Rows)
                {
                    ///Insert PartReceive
                    DateTime arrivaldate;
                    DateTime.TryParse(add_row["ArrivalDate"].ToString(), out arrivaldate);
                    try
                    {
                        myConnection.Open();
                        string sql = "INSERT INTO [241_PartReceive] (POID, OrderNo, Quantity, ArrivalDate, Note)"
                            + " VALUES ('" + add_row["POID"].ToString() + "', '" + add_row["OrderNo"].ToString() + "',"
                            + " " + add_row["Quantity"].ToString() + ", @arrivaldate, '" + add_row["Note"] + "')";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.Parameters.Add("@arrivaldate", SqlDbType.DateTime).Value = arrivaldate.Date;
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();
                        myConnection.Close();                        
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }

                        err += "Error: OrderNo " + add_row["OrderNo"].ToString() + ex.Message + " \n\r";
                    }

                    if (Int32.Parse(add_row["Full"].ToString()) == 1)
                    {                        
                        ///Update PartReceive to Finished
                        updatePartReceive(add_row["OrderNo"].ToString(), add_row["POID"].ToString());

                        ///Update PODetails to Finished
                        updatePODetails(add_row["OrderNo"].ToString(), add_row["POID"].ToString());


                        ///Check if PO Finished
                        int count = countNotFinishedOrderNo(add_row["POID"].ToString());
                        if (count == 0)
                        {
                            ///Update PO to Finished
                            updatePO(add_row["POID"].ToString());
                        }
                    }
                }
            }
            else
            {
                err = "There is no data to import";
            }

            if (string.IsNullOrEmpty(err))
            {
                MessageBox.Show("Data has been import succeedfully.");
            }
            else
            {
                MessageBox.Show(err);
            }

            this.ImportTable.Rows.Clear();
        }

        ////////////////////////////////////////////////////////
        /// Get total quantity part had been arrived before
        ////////////////////////////////////////////////////////
        private float getTotalQuantityArrived(string order, string po)
        {            
            float ret = 0;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SUM(Quantity) FROM [241_PartReceive] GROUP BY POID, OrderNo, Finished HAVING OrderNo='"
                    + order + "' AND POID='" + po + "' AND Finished=0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ret = float.Parse(dataReader[0].ToString());
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = 0;
            }
            return ret;
        }

        ////////////////////////////////////////////////////////
        /// Get total quantity ordered in PODetails
        ////////////////////////////////////////////////////////
        private float getTotalQuantityOrdered(string order, string po)
        {
            float ret = 0;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SUM(Quantity) FROM [241_PODetails] GROUP BY POID, OrderNo, Finished HAVING OrderNo='"
                    + order + "' AND POID='" + po + "' AND Finished=0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ret = float.Parse(dataReader[0].ToString());
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = 0;
            }
            return ret;
        }
        

        //////////////////////////////////////////////////
        /// Update PartRecive to Finished
        //////////////////////////////////////////////////
        private void updatePartReceive(string order, string po)
        {
            try
            {
                myConnection.Open();
                string sql = "UPDATE [241_PartReceive] SET Finished=1 WHERE POID='" + po + "'"
                    + " AND OrderNo='" + order + "' AND Finished=0";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("ERROR: Cannot update Arrival Part.");
            }
        }

        //////////////////////////////////////////////////
        /// Update PODetails to Finished
        //////////////////////////////////////////////////
        private void updatePODetails(string order, string po)
        {
            try
            {
                myConnection.Open();
                string sql = "UPDATE [241_PODetails] SET Finished=1 WHERE POID='" + po + "'"
                    + " AND OrderNo='" + order + "' AND Finished=0";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("ERROR: Cannot update Order " + order);
            }
        }

        //////////////////////////////////////////////////////////////
        /// Count all OrderNo have not finised of POID 
        //////////////////////////////////////////////////////////////
        private int countNotFinishedOrderNo(string po)
        {            
            int count = 1;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT COUNT(OrderNo) FROM [241_PODetails]"
                    + " GROUP BY POID, Finished HAVING POID='" + po + "' AND Finished=0";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    count = Int32.Parse(dataReader[0].ToString());
                }
                else
                {
                    count = 0;
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                count = 1;
            }
            return count;
        }

        //////////////////////////////////////////////////
        /// Update PO to Finished
        //////////////////////////////////////////////////
        private void updatePO(string po)
        {            
            try
            {
                myConnection.Open();
                string sql = "UPDATE [241_PO] SET Finished=1 WHERE ID='" + po + "'";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
                //MessageBox.Show("POID " + po + " is now finished");
                this.Dispose();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("ERROR: Cannot update PO: " + po);
            }
        }        

        private void importButton_Click(object sender, EventArgs e)
        {
            ImportExcel();
        }
    }
}
